package com.woniuxy.shop.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;

import com.woniuxy.shop.entity.Address;
import com.woniuxy.shop.entity.Goods;
import com.woniuxy.shop.entity.User;


/**
*@author 作者：
*version 创建时间：
*类说明：与用户相关的数据库联系
*/
public class UserDao {
	
	
	/**
	 * 开户
	 * @param u
	 */
	public void add(User u){
		String sql = "insert into user_info(account,password,avatar,status,score,email,phonenum) values(?,?,?,?,?,?,?)";
		Object[] obs = {u.getAccount(),u.getPassword(),u.getAvatar(),u.getStatus(),u.getScore(),u.getEmail(),u.getPhonenum()};
		DbHelper.excuteSQL(sql,obs); 
	}
	
	
	/**
	 * 更新用户信息
	 * @param u
	 */
	public void update(User u) {
		String sql = "update user_info set account=?,password=?,avatar=?,status=?,score=?,email=?,phonenum=? where id=?";
		Object[] obs = {u.getAccount(),u.getPassword(),u.getAvatar(),u.getStatus(),u.getScore(),u.getEmail(),u.getId(),u.getPhonenum()};
		DbHelper.excuteSQL(sql,obs); 
	}

	/**
	 * 获取指定页所有用户信息
	 * @return
	 * 
	 */
	public List<User> getUsers(int pagesize,int page){
		List<User> users = new ArrayList<>();
		String sql = "select * from user_info limit ?,?";
		users = DbHelper.executeQuery(sql, User.class,(page-1)*pagesize,pagesize);
		return users;
	}
	
	/**
	 * 获取总共的用户数量
	 * @return
	 */
	public int getTotalUsers() {
		String sql = "select count(*) from user_info";
		return ((Long)DbHelper.executeQueryForScalar(sql)).intValue();
	}
	
	/**
	 * 指定账号查询用户数量
	 * @param account
	 * @return 若为0表示没查到，为1表示查到有一个该账号的用户信息
	 */
	public int getNums(String account){
		String sql = "select count(*) from user_info where account=?";
		return ((Long)DbHelper.executeQueryForScalar(sql, account)).intValue();
	}
	

	/**
	 * 指定邮箱查询用户数量
	 * @param account
	 * @return 若为0表示没查到，为1表示查到有一个该账号的用户信息
	 */
	public int getNumsByEmail(String email){
		String sql = "select count(*) from user_info where email=?";
		return ((Long)DbHelper.executeQueryForScalar(sql, email)).intValue();
	}
	
	/**
	 * 查询指定账号的用户信息
	 * @param act
	 * @return
	 */
	public User getUserByAct(String act) {
		String sql = "select * from user_info where account=?";
		return DbHelper.executeQueryForSingleRow(sql, User.class, act);
	}
	
	/**
	 * 根据指定ID查询用户信息
	 * @param id
	 * @return
	 */
	public User getUserByID(int id) {
		String sql = "select * from user_info where id=?";
		return DbHelper.executeQueryForSingleRow(sql, User.class, id);
	}
	
	public int getUsersAsLimit(User u) {
		String sql = "select count(*) from user_info where 1=1";
		if(u.getAccount()!=null&&!u.getAccount().trim().equals("")) {
			sql += " and account like'%"+u.getAccount()+"%'";
		}
		if(u.getEmail()!=null&&!u.getEmail().trim().equals("")) {
			sql += " and email like'%"+u.getEmail()+"%'";
		}
		if(u.getStatus()!=null&&!u.getStatus().trim().equals("")) {
			sql += " and status='"+u.getStatus()+"'";
		}
		if(u.getMinscore()!=0) {
			sql += " and score>="+u.getMinscore();
		}
		if(u.getMaxscore()!=0) {
			sql += " and score<="+u.getMaxscore();
		}
		return ((Long)DbHelper.executeQueryForScalar(sql)).intValue();
	}


	public List<User> getUsersByPage(int page, int pagesize, User u) {
		String sql = "select * from user_info where 1=1";
		if(u.getAccount()!=null&&!u.getAccount().trim().equals("")) {
			sql += " and account like'%"+u.getAccount()+"%'";
		}
		if(u.getEmail()!=null&&!u.getEmail().trim().equals("")) {
			sql += " and email like'%"+u.getEmail()+"%'";
		}
		if(u.getStatus()!=null&&!u.getStatus().trim().equals("")) {
			sql += " and status='"+u.getStatus()+"'";
		}
		if(u.getMinscore()!=0) {
			sql += " and score>="+u.getMinscore();
		}
		if(u.getMaxscore()!=0) {
			sql += " and score>="+u.getMaxscore();
		}
		sql += " limit ?,?";
		return DbHelper.executeQuery(sql, User.class, (page-1)*pagesize,pagesize);
	}

	/**
	 * 根据邮箱更改用户状态
	 * @param status
	 * @param email
	 */
	public void active(String status,String email) {
		String sql = "update user_info set status=? where email=?";
		DbHelper.excuteSQL(sql, status,email);
	}

	
	/**
	 * 根据邮箱获取用户信息
	 * @param email
	 * @return
	 */
	public User getUserByEmail(String email) {
		String sql = "select * from user_info where email=?";
		return DbHelper.executeQueryForSingleRow(sql, User.class, email);
	}

	/**
	 * 更新指定用户的密码
	 * @param id
	 * @param newpwd
	 */
	public void updatePwd(int id, String newpwd) {
		String sql = "update user_info set password=? where id=?";
		DbHelper.excuteSQL(sql, newpwd,id);
		
	}


	public void uploadAvatar(int id, String image) {
		String sql = "update user_info set avatar=? where id=?";
		DbHelper.excuteSQL(sql, image,id);
		
	}
}
